package com.css.fxfzypg.modules.preAssessResult.repository.impl;

import com.css.fxfzypg.modules.preAssessResult.entity.SeiCapDistribution;
import com.css.fxfzypg.modules.preAssessResult.repository.PreAssessResultRepository;
import com.css.fxfzypg.modules.preAssessResult.vo.AreaBoundaryVO;
import com.css.fxfzypg.modules.preassesstaskmanage.vo.PreAssessVo;
import com.css.fxfzypg.modules.surveytaskmanage.vo.TaskVo;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author
 * @date 2022/10/13 17:04
 * @description
 **/
@Repository
public class PreAssessResultRepositoryImpl implements PreAssessResultRepository {
    @Resource
    JdbcTemplate jdbcTemplate;

    @Autowired
    SysAreaService sysAreaService;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public Map<String, Object> findParentIdByTask(String parentId, TaskVo taskVo, int curPage, int pageSize, Boolean flag) {
        StringBuilder orderSql = new StringBuilder(" order by finTask.task_num desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        StringBuilder oSql = new StringBuilder("select id,task_num,task_name,task_manager,task_status,group_name,group_leader,task_district,parent_task,assign_set_arthquake,parent_id from ");
        //父id为 0  的任务
        StringBuilder fSql = new StringBuilder("((select task.id,task.task_num,task.task_name,task.task_manager,task.task_status," +
                "task.group_name,task.group_leader,task.task_district,task.parent_task,task.assign_set_arthquake," +
                "task.parent_id from  ypg_preassess_task task,ypg_group grou ");
        StringBuilder fWhereSql = new StringBuilder(" where task.del_flag = '0' and task.parent_id = '" + parentId + "' " +
                "and task.task_status!='0' and grou.del_flag='0' and task.group_id=grou.id ");
        if(!taskVo.getIsGjAdmin()){
            fWhereSql.append(" and (task.group_leader_id = '" + PlatformSessionContext.getUserID() + "' or");
            fWhereSql.append(" grou.group_member like '%" + taskVo.getLoginName() + "%' or ");
            fWhereSql.append(" task.task_manager_id = '" + taskVo.getCreateUser() + "' or ");
            fWhereSql.append(" grou.group_expert like '%" + taskVo.getLoginName() + "%') ");
        }
        StringBuilder gWhereSql = new StringBuilder(" ");
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskNum())) {
            gWhereSql.append(" and task.task_num like '%" + taskVo.getTaskNum() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskName())) {
            gWhereSql.append(" and task.task_name like '%" + taskVo.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskStatus())) {
            gWhereSql.append(" and task.task_status = '" + taskVo.getTaskStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getProvince()) || !PlatformObjectUtils.isEmpty(taskVo.getCity()) || !PlatformObjectUtils.isEmpty(taskVo.getCounty())) {
            gWhereSql.append(" and (");
            if (!PlatformObjectUtils.isEmpty(taskVo.getProvince())) {
                String province = sysAreaService.getAreaNameByAreaId(taskVo.getProvince());
                gWhereSql.append(" task.task_district like '%" + province + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(taskVo.getCity())) {
                String city = sysAreaService.getAreaNameByAreaId(taskVo.getCity());
                gWhereSql.append(" or task.task_district like '%" + city + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(taskVo.getCounty())) {
                String county = sysAreaService.getAreaNameByAreaId(taskVo.getCounty());
                gWhereSql.append(" or task.task_district like '%" + county + "%'");
            }
            gWhereSql.append(") ");
        }
        StringBuilder unionSql = new StringBuilder(") union (");
        //父id不为 0 的任务
        StringBuilder zSql = new StringBuilder("select distinct task.id,task.task_num,task.task_name,task.task_manager," +
                "task.task_status,task.group_name,task.group_leader,task.task_district,task.parent_task," +
                "task.assign_set_arthquake,task.parent_id from ypg_preassess_task task join " +
                "(select task.id,task.task_num,task.task_name,task.task_manager,task.task_status," +
                "task.group_name,task.group_leader,task.task_district,task.parent_task,task.assign_set_arthquake," +
                "task.parent_id from ypg_preassess_task task left join ypg_group grou on task.group_id = grou.id ");
        StringBuilder zWhereSql = new StringBuilder(" where task.del_flag = '0' and task.parent_id != '0' " +
                "and task.assign_set_arthquake = '0' and task.task_status!='0' and grou.del_flag='0'");
        if(!taskVo.getIsGjAdmin()){
            zWhereSql.append(" and (task.group_leader_id = '" + PlatformSessionContext.getUserID() + "' or");
            zWhereSql.append(" grou.group_member like '%" + taskVo.getLoginName() + "%' or ");
            zWhereSql.append(" task.task_manager_id = '" + taskVo.getCreateUser() + "' or ");
            zWhereSql.append(" grou.group_expert like '%" + taskVo.getLoginName() + "%') ");
        }

        zWhereSql.append(gWhereSql);
        zWhereSql.append(") subTask on task.id = subTask.parent_id )) finTask");

        //sql合体 oSql + fsql + fwhere + gwhere + unionsql + zsql + zwhere + ordersql + limitsql
        StringBuilder finSql = oSql.append(fSql).append(fWhereSql).append(gWhereSql).append(unionSql).append(zSql).append(zWhereSql).append(orderSql);
        Integer total = jdbcTemplate.query(finSql.toString(), new BeanPropertyRowMapper<>(PreAssessVo.class)).size();
        StringBuilder sql = finSql.append(limitSql);
        List<PreAssessVo> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PreAssessVo.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
        /*StringBuilder sql = new StringBuilder("select task.id,task.task_num,task.task_name,task.task_manager,task.task_status,task.group_name,task.group_leader,task.task_district,task.parent_task,task.assign_set_arthquake,task.parent_id from  ypg_preassess_task task,ypg_group grou ");
        StringBuilder whereSql = new StringBuilder(" where task.del_flag = '0' and task.parent_id = '"+parentId+"' and task.task_status!='0' and grou.del_flag='0' and task.group_id=grou.id ");
        //StringBuilder whereSql = new StringBuilder(" where task.del_flag = '0' and task.task_status!='0' and grou.del_flag='0' and task.group_id=grou.id ");


        StringBuilder orderSql = new StringBuilder(" order by task.create_time desc");
        int firstIndex=(curPage-1) * pageSize;
        int lastIndex=pageSize;

        whereSql.append(" and (task.group_leader_id = '"+ PlatformSessionContext.getUserID()+"' or");
        whereSql.append(" grou.group_member like '%"+taskVo.getLoginName()+"%' or ");
        whereSql.append(" task.task_manager_id = '"+taskVo.getCreateUser()+"' or ");
        whereSql.append(" grou.group_expert like '%"+taskVo.getLoginName()+"%') ");

        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskNum())){
            whereSql.append(" and task.task_num like '%"+taskVo.getTaskNum()+"%'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskName())){
            whereSql.append(" and task.task_name like '%"+taskVo.getTaskName()+"%'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getTaskStatus())){
            whereSql.append(" and task.task_status = '"+taskVo.getTaskStatus()+"'");
        }
        if(!PlatformObjectUtils.isEmpty(taskVo.getProvince()) || !PlatformObjectUtils.isEmpty(taskVo.getCity()) || !PlatformObjectUtils.isEmpty(taskVo.getCounty())){
            whereSql.append(" and (");
            if(!PlatformObjectUtils.isEmpty(taskVo.getProvince())){
                whereSql.append(" task.task_district like '%"+ taskVo.getProvince() +"%'");
            }
            if(!PlatformObjectUtils.isEmpty(taskVo.getCity())){
                whereSql.append(" or task.task_district like '%"+ taskVo.getCity() +"%'");
            }
            if(!PlatformObjectUtils.isEmpty(taskVo.getCounty())){
                whereSql.append(" or task.task_district like '%"+ taskVo.getCounty() +"%'");
            }
            whereSql.append(") ");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PreAssessVo.class)).size();
        List<PreAssessVo> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(PreAssessVo.class));
        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",list);
        return map;*/

    }

    @Override
    public Map<String, Object> undistributedTaskPage(String parentId, TaskVo taskVo, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select id,task_num,task_name,task_manager,task_status,group_name,group_leader,task_district,parent_task,assign_set_arthquake,parent_id from ypg_preassess_task");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and parent_id = '" + parentId + "' " +
                "and (id in (select parent_id from ypg_preassess_task where  parent_id!='0' and del_flag = '0')) and (assign_set_arthquake IS NUll or assign_set_arthquake = '')");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = pageSize;


        if (!PlatformObjectUtils.isEmpty(taskVo.getCreateUser())) {
            whereSql.append(" and create_user = '" + taskVo.getCreateUser() + "'");
        }

        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskNum())) {
            whereSql.append(" and task_num like '%" + taskVo.getTaskNum() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskName())) {
            whereSql.append(" and task_name like '%" + taskVo.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskStatus())) {
            whereSql.append(" and task_status = '" + taskVo.getTaskStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getLoginName())) {
            whereSql.append(" and task_manager = '" + taskVo.getLoginName() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getProvince()) || !PlatformObjectUtils.isEmpty(taskVo.getCity()) || !PlatformObjectUtils.isEmpty(taskVo.getCounty())) {
            whereSql.append(" and (");
            if (!PlatformObjectUtils.isEmpty(taskVo.getProvince())) {
                whereSql.append(" task_district like '%" + taskVo.getProvince() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(taskVo.getCity())) {
                whereSql.append(" or task_district like '%" + taskVo.getCity() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(taskVo.getCounty())) {
                whereSql.append(" or task_district like '%" + taskVo.getCounty() + "%'");
            }
            whereSql.append(") ");
        }
        sql.append(whereSql).append(orderSql);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PreAssessVo.class)).size();
        List<PreAssessVo> list = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(PreAssessVo.class));
        Map<String, Object> map = new HashMap();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public List<PreAssessVo> findSubIdByTask(String parentId, TaskVo taskVo) {
        StringBuilder sql = new StringBuilder("select * from ypg_preassess_task");
        StringBuilder whereSql = new StringBuilder(" where del_flag = '0' and parent_id = '" + parentId + "' and task_status!='0'");
        StringBuilder orderSql = new StringBuilder(" order by create_time desc");

        /*if(!PlatformObjectUtils.isEmpty(taskVo.getCreateUser())){
            whereSql.append(" and create_user = '"+taskVo.getCreateUser()+"'");
        }*/

        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskNum())) {
            whereSql.append(" and task_num like '%" + taskVo.getTaskNum() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskName())) {
            whereSql.append(" and task_name like '%" + taskVo.getTaskName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getTaskStatus())) {
            whereSql.append(" and task_status = '" + taskVo.getTaskStatus() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(taskVo.getProvince()) || !PlatformObjectUtils.isEmpty(taskVo.getCity()) || !PlatformObjectUtils.isEmpty(taskVo.getCounty())) {
            whereSql.append(" and (");
            if (!PlatformObjectUtils.isEmpty(taskVo.getProvince())) {
                whereSql.append(" task_district like '%" + taskVo.getProvince() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(taskVo.getCity())) {
                whereSql.append(" or task_district like '%" + taskVo.getCity() + "%'");
            }
            if (!PlatformObjectUtils.isEmpty(taskVo.getCounty())) {
                whereSql.append(" or task_district like '%" + taskVo.getCounty() + "%'");
            }
            whereSql.append(") ");
        }
        String sqlWhole = sql.append(whereSql).append(orderSql).toString();

        List<PreAssessVo> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(PreAssessVo.class));
        return list;
    }

    @Override
    public List<AreaBoundaryVO> getAreaBoundaryVO1(String taskId) {
        String sql = "SELECT t.province,t.city,t.county,null as street," +
                "st_astext(st_intersection((select geom FROM ypg_preassess_task WHERE id = '" + taskId + "'),t.geom)) as geomText " +
                "FROM (select a.geom as geom,c.name as province,b.name as city,a.name as county from district_boundary_county a " +
                "left join district_boundary_city b on b.code=concat(subString(a.code,0,5),'00')" +
                "left join district_boundary_province c on c.code=concat(subString(a.code,0,3),'0000')) t " +
                "WHERE ST_Intersects(t.geom,(select a.geom FROM ypg_preassess_task a WHERE a.id = '" + taskId + "')) " +
                "and ST_Touches(t.geom,(select a.geom FROM ypg_preassess_task a WHERE a.id = '" + taskId + "'))!='t'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AreaBoundaryVO.class));
    }

    @Override
    public List<AreaBoundaryVO> getAreaBoundaryVO2(String taskId) {
        String sql = "SELECT t.province,t.city,t.county,t.street," +
                "st_astext(st_intersection((select geom FROM ypg_preassess_task WHERE id = '" + taskId + "'),t.geom)) as geomText " +
                "FROM data_area t WHERE ST_Intersects(t.geom,(select a.geom FROM ypg_preassess_task a WHERE a.id = '" + taskId + "')) " +
                "and is_delete='0' and ST_Touches(t.geom,(select a.geom FROM ypg_preassess_task a WHERE a.id = '" + taskId + "'))!='t'";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(AreaBoundaryVO.class));
    }

    @Override
    public void saveSeismicCapacity(SeiCapDistribution seiCapDistribution) {
        String sql = "insert into seismic_capacity_distribution(id, task_id, province, city, county, seismic_capacity_value, geom, pbe_id, dz_intensity, data_source, town) " +
                "values(:id, :taskId, :province, :city, :county, :seismicCapacityValue, ST_GeometryFromText(:geom,4490), :pbeId, :dzIntensity, :dataSource, :town)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(seiCapDistribution);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(sql, source, keyHolder);
    }

    @Override
    public SeiCapDistribution findByTaskIdLimit(String taskId, String type) {
        StringBuilder sql = new StringBuilder("select *,ST_AsText(geom) geom from seismic_capacity_distribution" +
                " where 1=1 and task_id = '" + taskId + "' and data_source = '" + type + "' limit 1");
        List<SeiCapDistribution> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SeiCapDistribution.class));
        return list.size() < 1 ? null : list.get(0);
    }

    @Override
    public List<SeiCapDistribution> getDataByTaskId(String taskId,String type) {
        StringBuilder sql = new StringBuilder("select seismic_capacity_value,dz_intensity,ST_AsText(geom) geomText from seismic_capacity_distribution where 1=1 and task_id = '" + taskId + "' and data_source = '" + type + "'");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SeiCapDistribution.class));
    }

    @Override
    public List<SeiCapDistribution> getSeiCapCalculation(String taskId, String dzIntensity, String type) {
        StringBuilder sql = new StringBuilder("select *,ST_AsText(geom) geom from seismic_capacity_distribution");
        StringBuilder whereSql = new StringBuilder(" where 1=1");
        if (!PlatformObjectUtils.isEmpty(taskId)) {
            whereSql.append(" and task_id = '" + taskId + "'");
        }
        if (!PlatformObjectUtils.isEmpty(dzIntensity)) {
            whereSql.append(" and dz_intensity = '" + dzIntensity + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(type)) {
            whereSql.append(" and data_source = '" + type + "'");
        }
        sql.append(whereSql);
        List<SeiCapDistribution> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SeiCapDistribution.class));
        return list;
    }

    @Override
    public void delSeiCapCalByTaskId(String taskId, String type) {
        String sql = "delete from seismic_capacity_distribution where task_id='" + taskId + "' and data_source = '" + type +
                "'";
        jdbcTemplate.update(sql);
    }

    @Override
    public int deleteSeiCapdistribution(String taskId) {
        String sql = "delete from seismic_capacity_distribution where task_id='" + taskId + "'";
        return jdbcTemplate.update(sql);
    }

    @Override
    public String getExtent(String taskId) {
        String sql = "select ST_extent(geom) from ypg_preassess_task where id='" + taskId + "'";
        return jdbcTemplate.queryForObject(sql, String.class);
    }

    @Override
    public String getcenter(String taskId) {
        String sql = "select ST_AsText(ST_Centroid(ST_extent(geom))) from ypg_preassess_task where id='" + taskId + "'";
        return jdbcTemplate.queryForObject(sql, String.class);
    }

    @Override
    @Transactional(propagation= Propagation.REQUIRES_NEW)
    public int saveScdList(List<SeiCapDistribution> list) {
        try {
            String sql = "insert into seismic_capacity_distribution(id, task_id, province, city, county, seismic_capacity_value, geom, pbe_id, dz_intensity, data_source, town) " +
                    "values(:id, :taskId, :province, :city, :county, :seismicCapacityValue, ST_GeometryFromText(:geom,4490), :pbeId, :dzIntensity, :dataSource, :town)";
            int[] ints = namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(list));
            return ints.length;
        }catch (Exception e){
            throw new RuntimeException("批量插入失败", e);
        }
    }
}
